package org.huel.backend.mapper;

import org.apache.ibatis.annotations.*;
import org.huel.backend.domain.entity.AbsencePrediction;

import java.util.List;

@Mapper
public interface AbsencePredictionMapper {

    /**
     * 查询指定学生的缺勤预测记录
     * @param studentId 学生ID
     * @return 学生的缺勤预测记录
     */
    @Select("SELECT * FROM absence_prediction WHERE student_id = #{studentId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "probability", column = "probability"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> selectByStudentId(@Param("studentId") String studentId);

    /**
     * 查询指定课程的缺勤预测记录
     * @param courseName 课程名称
     * @return 课程的缺勤预测记录
     */
    @Select("SELECT * FROM absence_prediction WHERE course_name = #{courseName}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "probability", column = "probability"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> selectByCourseName(@Param("courseName") String courseName);

    /**
     * 查询缺勤概率超过指定阈值的预测记录
     * @param threshold 概率阈值
     * @return 高风险缺勤预测记录
     */
    @Select("SELECT * FROM absence_prediction WHERE probability >= #{threshold} ORDER BY probability DESC")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "probability", column = "probability"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> selectHighRiskAbsences(@Param("threshold") float threshold);

    /**
     * 查询学生在指定课程的缺勤预测记录
     * @param studentId 学生ID
     * @param courseName 课程名称
     * @return 学生在指定课程的缺勤预测记录
     */
    @Select("SELECT * FROM absence_prediction WHERE student_id = #{studentId} AND course_name = #{courseName}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "probability", column = "probability"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    AbsencePrediction selectByStudentIdAndCourseName(@Param("studentId") String studentId, @Param("courseName") String courseName);

    /**
     * 查询排名前N的高风险缺勤预测记录
     * @param limit 限制返回的记录数
     * @return 高风险缺勤预测记录列表
     */
    @Select("SELECT * FROM absence_prediction ORDER BY probability DESC LIMIT #{limit}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "probability", column = "probability"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> selectTopRiskStudents(@Param("limit") int limit);

    /**
     * 插入新的缺勤预测记录
     * @param absencePrediction 缺勤预测实体
     * @return 影响的行数
     */
    @Insert("INSERT INTO absence_prediction (student_id, course_name, probability, rank_order) VALUES (#{studentId}, #{courseName}, #{probability}, #{rankOrder})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertAbsencePrediction(AbsencePrediction absencePrediction);

    /**
     * 更新缺勤预测记录
     * @param absencePrediction 缺勤预测实体
     * @return 影响的行数
     */
    @Update("UPDATE absence_prediction SET probability = #{probability}, rank_order = #{rankOrder} WHERE id = #{id}")
    int updateAbsencePrediction(AbsencePrediction absencePrediction);

    /**
     * 获取前N名最可能缺勤的学生
     * @param limit 限制数量
     * @return 缺勤预测列表
     */
    @Select("SELECT * FROM absence_prediction ORDER BY probability DESC, rank_order ASC LIMIT #{limit}")
    @Results({
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> getTopAbsencePredictions(@Param("limit") int limit);

    /**
     * 根据课程获取最可能缺勤的学生
     * @param courseName 课程名称
     * @param limit 限制数量
     * @return 缺勤预测列表
     */
    @Select("SELECT * FROM absence_prediction WHERE course_name = #{courseName} ORDER BY probability DESC, rank_order ASC LIMIT #{limit}")
    @Results({
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> getTopAbsencePredictionsByCourse(@Param("courseName") String courseName, @Param("limit") int limit);

    /**
     * 插入或更新缺勤预测数据
     * @param prediction 缺勤预测对象
     */
    @Insert("INSERT INTO absence_prediction (student_id, course_name, probability, rank_order, last_updated) " +
            "VALUES (#{studentId}, #{courseName}, #{probability}, #{rankOrder}, NOW()) " +
            "ON DUPLICATE KEY UPDATE " +
            "probability = VALUES(probability), " +
            "rank_order = VALUES(rank_order), " +
            "last_updated = NOW()")
    void insertOrUpdate(AbsencePrediction prediction);

    /**
     * 批量插入或更新缺勤预测数据
     * @param predictions 缺勤预测列表
     */
    @Insert("<script>" +
            "INSERT INTO absence_prediction (student_id, course_name, probability, rank_order, last_updated) VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{item.studentId}, #{item.courseName}, #{item.probability}, #{item.rankOrder}, NOW())" +
            "</foreach>" +
            "ON DUPLICATE KEY UPDATE " +
            "probability = VALUES(probability), " +
            "rank_order = VALUES(rank_order), " +
            "last_updated = NOW()" +
            "</script>")
    void batchInsertOrUpdate(@Param("list") List<AbsencePrediction> predictions);

    /**
     * 获取所有课程列表
     * @return 课程名称列表
     */
    @Select("SELECT DISTINCT course_name FROM absence_prediction ORDER BY course_name")
    List<String> getAllCourses();

    /**
     * 根据学生ID获取预测信息
     * @param studentId 学生ID
     * @return 预测列表
     */
    @Select("SELECT * FROM absence_prediction WHERE student_id = #{studentId} ORDER BY probability DESC")
    @Results({
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "rankOrder", column = "rank_order"),
            @Result(property = "lastUpdated", column = "last_updated")
    })
    List<AbsencePrediction> getPredictionsByStudentId(@Param("studentId") String studentId);
}